﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Text;
using System.Data.SqlClient;


namespace CSharp.Common.ExcelToDB
{
    public class ExcelToDBHelper
    {
        private string excelConnectionString;
        private string excelFilePath;
        private string sqlConnectionString = string.Empty;

        public void SetSqlConnectionString(string connectionString)
        {
            this.sqlConnectionString = connectionString;
        }

        public void SetExcelConnectionString(string excelFilePath)
        {
            this.excelFilePath = excelFilePath;
            this.excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
            //office 2003
            //conStringExcel = "Provider=Microsoft.Jet.OleDb.4.0;" + "Data Source=" + excelFilePath + ";" + "Extended ProPerties= \"Excel 8.0;HDR=Yes;\"";
            //office 2007or2010
            // conStringExcel = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFilePath + ";" + "Extended ProPerties= \"Excel 12.0;HDR=Yes;\"";
        }

        private List<string> GetExcelSheetName()
        {
            List<string> sheetNames = new List<string>();
            string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                excelFilePath + ";Extended Properties=Excel 8.0;";
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                con.Open();
                DataTable sheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
                con.Close();
                foreach (DataRow var in sheetName.Rows)
                {
                    sheetNames.Add(var[2].ToString());
                }
            }
            return sheetNames;
        }

        private bool InsertExcelDataToSql(List<string> sqls)
        {
            bool flag = false;
            if (sqlConnectionString != null)
            {
                using (SqlConnection con = new SqlConnection(sqlConnectionString))
                {
                    con.Open();
                    SqlTransaction tran = con.BeginTransaction();
                    SqlCommand cmd = new SqlCommand("", con, tran);
                    try
                    {
                        foreach (string var in sqls)
                        {
                            cmd.CommandText = var;
                            cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                        flag = true;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                    }
                }
            }
            return flag;
        }

        private List<string> CreateSqlFromExcel(DataSet ds)
        {
            List<string> sqls = new List<string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                StringBuilder sql = new StringBuilder();
                string isbn = ds.Tables[0].Rows[i]["ISBN"].ToString();  
                string name = ds.Tables[0].Rows[i]["BookName"].ToString();  
                string cong_name = ds.Tables[0].Rows[i]["CongName"].ToString();  
                string author = ds.Tables[0].Rows[i]["Author"].ToString();  
                string pubAddress = ds.Tables[0].Rows[i]["PubAddress"].ToString();  
                string publish = ds.Tables[0].Rows[i]["PublishName"].ToString();  
                //--------------------------------------------------

                //---------------------------------------------------
                sql.Append("insert into Book values (");
                sql.AppendFormat("'{0}','{1}','{2}',", isbn, name, cong_name);
                sql.AppendFormat("'{0}','{1}','{2}')", author, pubAddress, publish);


                sqls.Add(sql.ToString());
            }
            return sqls;
        }

    
        public List<string> InsertExcelDataToSql(out List<string> fails)
        {
            List<string> sheets = new List<string>();
            List<string> sheetNames = GetExcelSheetName();
            fails = new List<string>();
            OleDbConnection cnnxls = new OleDbConnection(excelConnectionString);
            for (int i = 0; i < sheetNames.Count; i++)
            {
                DataSet ds = new DataSet();
                OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetNames[i] + "]", cnnxls);
                myDa.Fill(ds);
                List<string> sqls = CreateSqlFromExcel(ds);
   
                if (InsertExcelDataToSql(sqls))
                {
                    sheets.Add(sheetNames[i]);
                }
                else
                {
                    fails.Add(sheetNames[i]);
                }
            }
            return sheets;
        }

        public bool InsertExcelDataToSql(string sheetName)
        {
            bool flag = false;
            DataSet ds = new DataSet();
            OleDbConnection con = new OleDbConnection(excelConnectionString);
            OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [" + sheetName + "$]", con);
            myDa.Fill(ds);
            con.Close();
            List<string> sqls = CreateSqlFromExcel(ds);
            return flag = InsertExcelDataToSql(sqls);
        }
    }

}